package com.witmed.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.witmed.dao.UserDao;
import com.witmed.entity.Appointment;
import com.witmed.entity.Department;
import com.witmed.entity.Doctor;
import com.witmed.entity.Hospital;
import com.witmed.entity.UserInfo;
import com.witmed.util.DBUtils;

/**
 * 鐢ㄦ埛Dao瀹炵幇绫�
 * 
 * @author lyx5953
 *
 */
public class UserDaoImpl implements UserDao {

	DBUtils dbutils = new DBUtils();

	@Override
	public List<UserInfo> queryAll() {
		String str = "select user_id," + "user_name," + "user_password," + "user_age," + "user_tel," + "user_sex,"
				+ "user_real_name," + "user_identify_id," + "user_picture_path " + "from user";
		ResultSet rs = dbutils.query(str);
		List<UserInfo> list = new ArrayList<UserInfo>();
		UserInfo user = new UserInfo();
		try {
			while (rs.next()) {
				user.setUserId(rs.getInt("user_id"));
				user.setUserName(rs.getString("user_name"));
				user.setUserPassword(rs.getString("user_password"));
				user.setUserAge(rs.getInt("user_age"));
				user.setUserTel(rs.getString("user_tel"));
				user.setUserSex(rs.getInt("user_sex"));
				user.setUserRealName(rs.getString("user_real_name"));
				user.setUserIdentifyId(rs.getString("user_identify_id"));
				user.setUserPicturePath(rs.getString("user_picture_path"));
				list.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	@Override
	public List<UserInfo> queryByUserName(String userName){
		String str = "select user_id,"+
					 "user_name,"+
					 "user_password,"+
					 "user_age,"+
					 "user_tel,"+
					 "user_sex,"+
					 "user_real_name,"+
					 "user_identify_id,"+
					 "user_picture_path "+
					 "from user where user_name=?";
		ResultSet rs = dbutils.query(str,userName);
		UserInfo user = new UserInfo();
		List<UserInfo> list = new ArrayList<UserInfo>();
		try {
			while(rs.next()) {
				user.setUserId(rs.getInt("user_id"));
				user.setUserName(rs.getString("user_name"));
				user.setUserPassword(rs.getString("user_password"));
				user.setUserAge(rs.getInt("user_age"));
				user.setUserTel(rs.getString("user_tel"));
				user.setUserSex(rs.getInt("user_sex"));
				user.setUserRealName(rs.getString("user_real_name"));
				user.setUserIdentifyId(rs.getString("user_identify_id"));
				user.setUserPicturePath(rs.getString("user_picture_path"));
				list.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	@Override
	public List<UserInfo> queryByUserTel(String userTel){
		String str = "select user_id,"+
					 "user_name,"+
					 "user_password,"+
					 "user_age,"+
					 "user_tel,"+
					 "user_sex,"+
					 "user_real_name,"+
					 "user_identify_id,"+
					 "user_picture_path "+
					 "from user where user_tel=?";
		ResultSet rs = dbutils.query(str,userTel);
		UserInfo user = new UserInfo();
		List<UserInfo> list = new ArrayList<UserInfo>();
		try {
			while(rs.next()) {
				user.setUserId(rs.getInt("user_id"));
				user.setUserName(rs.getString("user_name"));
				user.setUserPassword(rs.getString("user_password"));
				user.setUserAge(rs.getInt("user_age"));
				user.setUserTel(rs.getString("user_tel"));
				user.setUserSex(rs.getInt("user_sex"));
				user.setUserRealName(rs.getString("user_real_name"));
				user.setUserIdentifyId(rs.getString("user_identify_id"));
				user.setUserPicturePath(rs.getString("user_picture_path"));
				list.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	@Override
	public List<UserInfo> queryByUserNameAndPassword(String userName,String userPassword){
		String str = "select user_id,"+
					 "user_name,"+
					 "user_password,"+
					 "user_age,"+
					 "user_tel,"+
					 "user_sex,"+
					 "user_real_name,"+
					 "user_identify_id,"+
					 "user_picture_path "+
					 "from user where user_name=? and user_password=?";
		ResultSet rs = dbutils.query(str,userName,userPassword);
		UserInfo user = new UserInfo();
		List<UserInfo> list = new ArrayList<UserInfo>();
		try {
			while(rs.next()) {
				user.setUserId(rs.getInt("user_id"));
				user.setUserName(rs.getString("user_name"));
				user.setUserPassword(rs.getString("user_password"));
				user.setUserAge(rs.getInt("user_age"));
				user.setUserTel(rs.getString("user_tel"));
				user.setUserSex(rs.getInt("user_sex"));
				user.setUserRealName(rs.getString("user_real_name"));
				user.setUserIdentifyId(rs.getString("user_identify_id"));
				user.setUserPicturePath(rs.getString("user_picture_path"));
				list.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	@Override
	public List<UserInfo> queryByUserTelAndPassword(String userTel,String userPassword){
		String str = "select user_id,"+
					 "user_name,"+
					 "user_password,"+
					 "user_age,"+
					 "user_tel,"+
					 "user_sex,"+
					 "user_real_name,"+
					 "user_identify_id,"+
					 "user_picture_path "+
					 "from user where user_tel=? and user_password=?";
		ResultSet rs = dbutils.query(str,userTel,userPassword);
		UserInfo user = new UserInfo();
		List<UserInfo> list = new ArrayList<UserInfo>();
		try {
			while(rs.next()) {
				user.setUserId(rs.getInt("user_id"));
				user.setUserName(rs.getString("user_name"));
				user.setUserPassword(rs.getString("user_password"));
				user.setUserAge(rs.getInt("user_age"));
				user.setUserTel(rs.getString("user_tel"));
				user.setUserSex(rs.getInt("user_sex"));
				user.setUserRealName(rs.getString("user_real_name"));
				user.setUserIdentifyId(rs.getString("user_identify_id"));
				user.setUserPicturePath(rs.getString("user_picture_path"));
				list.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int insert(UserInfo user) {
		String str = "insert into user(user_name,user_password,user_age,user_tel,user_sex,user_real_name,user_identify_id,user_picture_path) values(?,?,?,?,?,?,?,?)";
		int i = dbutils.update(str, user.getUserName(), user.getUserPassword(), user.getUserAge(), user.getUserTel(),
				user.getUserSex(), user.getUserRealName(), user.getUserIdentifyId(), user.getUserPicturePath());
		return i;
	}

	@SuppressWarnings("static-access")
	@Override
	public int update(UserInfo user) {
		String str = "update user set user_name=?,user_password=?,user_age=?,user_tel=?,user_sex=?,user_real_name=?,user_identify_id=?,user_picture_path=? where user_id=?";
		int i = dbutils.update(str, user.getUserName(),user.getUserPassword() , user.getUserAge(), user.getUserTel(),
				user.getUserSex(), user.getUserRealName(), user.getUserIdentifyId(), user.getUserPicturePath(),
				user.getUserId());
		return i;
	}
	
	@Override
	public int updateByTel(UserInfo user) {
		String str = "update user set user_password=? where user_tel=?";
		int i = dbutils.update(str, user.getUserPassword() , user.getUserTel());
		return i;
	}

	@Override
	public int delete(int userId) {
		String str = "delete from user where user_id=?";
		int i = dbutils.update(str, userId);
		return i;
	}

	/**
	 * 鐢ㄦ埛鏌ヨ涓汉淇℃伅閫氳繃鐢ㄦ埛id鎴栫敤鎴风殑鎵嬫満鍙�
	 * 
	 * @author ChenYouming
	 * @param number
	 *            浼犲叆鐨勬煡璇㈡潯浠跺��
	 * @return 杩斿洖涓�涓敤鎴烽泦鍚�
	 */
	@Override
	public List<UserInfo> queryByUserIdOrUserTel(String number) {
		String sql = "select user_id," + 
					 "user_name," + 
					 "user_password," + 
					 "user_age," +
					 "user_tel," + 
					 "user_sex,"+ 
					 "user_real_name," + 
					 "user_identify_id," + 
					 "user_picture_path "+
					 "from user where user_id=? or user_tel=?";
		ResultSet resultSet = dbutils.query(sql, number, number);
		List<UserInfo> list = new ArrayList<UserInfo>();
		try {
			while (resultSet.next()) {
				Integer userId = resultSet.getInt(1);
				String userName = resultSet.getString(2);
				String userPassword=resultSet.getString(3);
				Integer userAge = resultSet.getInt(4);
				String userTel = resultSet.getString(5);
				Integer userSex = resultSet.getInt(6);
				String userRealName = resultSet.getString(7);
				String userIdentifyId = resultSet.getString(8);
				UserInfo userInfo = new UserInfo(userId, userName,userPassword, userAge, userTel, userSex, userRealName,
						userIdentifyId, null);
				list.add(userInfo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 鐢ㄦ埛鏌ヨ涓汉闂瘖淇℃伅閫氳繃闂瘖璁板綍id鎴栫敤鎴穒d
	 * 
	 * @author ChenYouming
	 * @param number浼犲叆鐨勬煡璇㈡潯浠跺��
	 * @return 杩斿洖涓�涓棶璇婅褰曢泦鍚�
	 */
	@Override
	public List<Appointment> queryByUserId(String number) {
		StringBuffer stringBuffer = new StringBuffer();
		stringBuffer.append("select ")
				.append("ui.appointment_id,")
				.append("ui.appointment_time,")
				.append("u.user_name,")
				.append("d.doctor_name,")
				.append("dp.department_name,")
				.append("h.hospital_name")
				.append(" from doctor d")
				.append(" right join department dp")
				.append(" on d.department_id = dp.department_id")
				.append(" right join hospital h")
				.append(" on dp.hospital_id =h.hospital_id")
				.append(" right join appointment ui")
				.append(" on d.doctor_id =ui.doctor_id")
				.append(" right join user u")
				.append(" on u.user_id =ui.user_id")
				.append(" WHERE  ui.user_id= ? order by ui.appointment_time asc ");
		ResultSet resultSet = dbutils.query(stringBuffer.toString(), number);
		List<Appointment> list = new ArrayList<Appointment>();
		try {
			while (resultSet.next()) {
				Integer appointmentId = resultSet.getInt(1);
				String appointmentTime = resultSet.getString(2);
				String userName = resultSet.getString(3);
				String doctorName = resultSet.getString(4);
				String hospitalName = resultSet.getString(6);
				String departmentName = resultSet.getString(5);
				Hospital hospital = new Hospital(null, hospitalName);
				Department department = new Department(null, departmentName, hospital);
				UserInfo userInfo = new UserInfo(null, userName, null, null, null, null, null, null, null);
				Doctor doctor = new Doctor(null, doctorName, null, null, null, null, null, department);
				Appointment appointment = new Appointment(appointmentId, appointmentTime,doctor,userInfo);
				list.add(appointment);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

}
